package epam.bookShelf.database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.apache.log4j.Logger;

import epam.bookShelf.bookStuff.Book;
import epam.bookShelf.bookStuff.BookBuilder;

/**
 *	Book DAO. Implements CRUD for Book instance 
 * @author Pustovit Michael, pustovitm@gmail.com
 */
public class BookDAO {       
	Logger log = null;
	
	private BookShelfDAO dao;
	
	//Name of Book table
    public static final String TABLE_NAME = "book";
    
    //Names of columns
    public static final String ID = "ID";
    public static final String TITLE = "TITLE";
    public static final String ISBN = "ISBN";
    public static final String DATE = "DAT";
    public static final String READED_DATE = "READED_DATE";
    public static final String DESCRIPTION = "DESCRIPTION";   
    public static final String BORROW_RECORD_ID = "BORROW_RECORDID";
    public static final String AUTHOR_ID = "AUTHORID";
    public static final String LOCATION_ID = "LOCATIONID";
	
	/**
	 * Standard constructor for Book DAO instance
	 * @param dao	DAO which implements basic operation with base
	 */
	public BookDAO(BookShelfDAO dao) {
		this.dao = dao;
		
		Logger.getLogger(AuthorDAO.class);
	}
	
	/**
	 * Writing book into DB
	 * @param book	book instance
	 * @throws SQLException	problems with DB
	 */
	public void writeBook(Book book) throws SQLException {
        Connection conn = null;
        PreparedStatement prepStat = null;
        
        try {
            conn = dao.getConnection();
            prepStat = conn.prepareStatement(
                    "INSERT INTO " + TABLE_NAME + " (" 
                    + TITLE            + ","
                    + ISBN             + ","
                    + DATE             + ","
                    + READED_DATE      + ","
                    + DESCRIPTION      + ","
                    + BORROW_RECORD_ID + ","
                    + AUTHOR_ID      + ","
                    + LOCATION_ID
                    + ") VALUES (?, ?, ?, ?, ?, ?, ?, ?);");
            
            fillBookFields(book, prepStat);
            
            prepStat.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            log.error("Write book error");
        } finally {
            dao.closeStatement(prepStat);
            dao.closeConnection(conn);
        }
    }
    
    /**
     * Read book from DB.
     * @param id ID of book
     * @return book with referred ID. If such book doesn't exist - NULL
     * @throws SQLException problems with DB
     */
    public Book readBookByID(long id) throws SQLException {       
        Connection conn = null;
        PreparedStatement prepStat = null;
        Book rez = null;
        
        try {
            conn = dao.getConnection();
            prepStat = conn.prepareStatement(
                    "SELECT * FROM " + TABLE_NAME + " WHERE "
                    + ID + " = ?;");
            
            prepStat.setLong(1, id);
            ResultSet resultSet = prepStat.executeQuery();            
            
            while (resultSet.next()) {
            	rez = parseBookFromRezSet(resultSet);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            log.error("SQL error while book reading");
        } finally {
        	dao.closeStatement(prepStat);
        	dao.closeConnection(conn);             
        }
        
        return rez;
    }
    
    /**
     * Read book by title
     * @param title searching title
     * @return	list of books with referred title
     * @throws SQLException problems with DB
     */
    public ArrayList<Book> readBookByTitle(String title) throws SQLException {
        Connection conn = null;
        PreparedStatement prepStat = null;
        ArrayList<Book> rez = null;
        
        try {
            conn = dao.getConnection();
            prepStat = conn.prepareStatement(
            		"SELECT * FROM " + TABLE_NAME + " WHERE " + "title = ?;");
            
            rez = new ArrayList<Book>();
            
            prepStat.setString(1, title);
            
            ResultSet resultSet = prepStat.executeQuery();

            while (resultSet.next()) {
            	rez.add(parseBookFromRezSet(resultSet));
            }
                      
        } catch (SQLException e) {
            e.printStackTrace();
            log.error("SQL error while book reading");
        } finally {
            dao.closeStatement(prepStat);
            dao.closeConnection(conn);             
        }
		
		return rez;
	}
    
    /**
     * Updating book record
     * @param book updating book
     * @throws SQLException problems with DB
     */
    public void updateBook(Book book) throws SQLException {
        Connection conn = null;
        PreparedStatement prepStat = null;
        
        try {
            conn = dao.getConnection();
            prepStat = conn.prepareStatement(
                    "UPDATE " + TABLE_NAME + " SET " 
                    + TITLE            + " = ?,"
                    + ISBN             + " = ?,"
                    + DATE             + " = ?,"
                    + READED_DATE      + " = ?,"
                    + DESCRIPTION      + " = ?,"
                    + BORROW_RECORD_ID + " = ?,"
                    + AUTHOR_ID      + " = ?,"
                    + LOCATION_ID		+ " = ?"
                    + " WHERE " + ID + " = ?;");
            
            fillBookFields(book, prepStat);
            
            prepStat.setLong(9, book.getId());
            
            prepStat.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            log.error("SQL error while book updating");
        } finally {
        	dao.closeStatement(prepStat);
        	dao.closeConnection(conn);
        }
    }    

    /**
     * Deleting book with referred ID
     * @param id ID of a deleting book
     * @throws SQLException problems with DB
     */
    public void deleteBook(long id) throws SQLException {        
        Connection conn = null;
        PreparedStatement prepStat = null;
        
        try {
            conn = dao.getConnection();
            prepStat = conn.prepareStatement(
                    "DELETE FROM " + TABLE_NAME + " WHERE " 
                    + ID + " = ?;");
            
            prepStat.setLong(1, id);
            prepStat.executeUpdate();            
        } catch (SQLException e) {
            e.printStackTrace();
            log.error("SQL error while book deleating");
        } finally {
        	dao.closeStatement(prepStat);
        	dao.closeConnection(conn);             
        }
    }    
	
    protected Book parseBookFromRezSet(ResultSet resultSet) throws SQLException {
        long tempLong;
        String tempString;
    	
    	BookBuilder builder = new BookBuilder();
        tempLong = resultSet.getLong(ID);
        if (!resultSet.wasNull()) {
        	builder.setId(tempLong);
        }
        
        tempString = resultSet.getString(TITLE);
        if (!resultSet.wasNull()) {
        	builder.setTitle(tempString);
        }
        
        tempLong = resultSet.getLong(ISBN);
        if (!resultSet.wasNull()) {
        	builder.setIsbn(tempLong);
        }
        
        tempLong = resultSet.getLong(DATE);
        if (!resultSet.wasNull()) {
        	builder.setDat(tempLong);
        }
        
        tempLong = resultSet.getLong(READED_DATE);
        if (!resultSet.wasNull()) {
        	builder.setReadedDate(tempLong);
        }
        
        tempString = resultSet.getString(DESCRIPTION);
        if (!resultSet.wasNull()) {
        	builder.setDescription(tempString);
        }
        
        tempLong = resultSet.getLong(BORROW_RECORD_ID);                
        if (!resultSet.wasNull()) {
        	builder.setBorrowRecordId(tempLong);
        }                  
        
        tempString = resultSet.getString(AUTHOR_ID);
        if (!resultSet.wasNull()) {
        	builder.setAuthorName(tempString);
        }
        
        tempLong = resultSet.getLong(LOCATION_ID);                
        if (!resultSet.wasNull()) {
        	builder.setLocationId(tempLong);
        }
        
        return builder.getBook();
    }
    
    protected void fillBookFields(Book book, 
    		PreparedStatement prepStat) throws SQLException
    {
        //TITLE
    	prepStat.setString(1, book.getTitle());
        
    	//ISBN
        if (book.getIsbn() >= 0) {
            prepStat.setLong(2, book.getIsbn());    
        } else {
            prepStat.setNull(2, java.sql.Types.BIGINT);
        }
        
        //DAT       
        prepStat.setLong(3, book.getDat());
        
        //Readed_date
        if (book.getReadedDate() >= 0) {
            prepStat.setLong(4, book.getReadedDate());
        } else {
            prepStat.setNull(4, java.sql.Types.BIGINT);
        }
        
        //Description        
        if (book.getDescription() != null) {
            prepStat.setString(5, book.getDescription());
        } else {
            prepStat.setNull(5, java.sql.Types.VARCHAR);
        }
        
        //Borrow_Recordid
        if (book.getBorrowRecordId() >= 0) {
        	prepStat.setLong(6, book.getBorrowRecordId());
        } else {
        	prepStat.setNull(6, java.sql.Types.INTEGER);
        }
        
        
        if (book.getAuthorName() != null) {
        	prepStat.setString(7, book.getAuthorName());
        } else {
        	prepStat.setNull(7, java.sql.Types.INTEGER);
        }
        
        if (book.getLocationId() >= 0) {
        	prepStat.setLong(8, book.getLocationId());
        } else {
        	prepStat.setNull(8, java.sql.Types.INTEGER);
        }
    }
}
